/************* QnaBoardDAO Query *****************/
-- 게시물 (board)
drop table board cascade constraints purge;
create table board (
	contentId number primary key,
	groupId number not null,
	groupPosition number not null,
	groupDepth number not null,
	category varchar2(20),
	title varchar2(100) not null,
	content varchar2(4000) not null,
	regDate date not null,
	modDate date not null,
	hits number not null,
	isNotice varchar2(10) not null,
	commentHits number not null,
	boardId number not null,
	memberId varchar2(30) not null,
	locationId number not null,
	foreign key(locationId) REFERENCES travelzone(locationId),
	foreign key(boardId) REFERENCES boardList(boardId) on delete cascade,
	foreign key(memberId) REFERENCES member(memberId)
);
SELECT * FROM BOARD;
/***********************************************************************************/
public int getSelectContentSeq();
/***********************************************************************************/
SELECT board_seq.nextval FROM dual



/***********************************************************************************/
public int getSelectCommentsSeq();
/***********************************************************************************/
SELECT comments_seq.nextval FROM dual



/***********************************************************************************/
public int getSelectAttachmentSeq();
/***********************************************************************************/
SELECT attachment_seq.nextval FROM dual



/***********************************************************************************/
public void insertContent(BoardVO boardVO);
/***********************************************************************************/
INSERT INTO board values(1,1,1,1,1,'첫글입니다.','반갑습니다!!!!!',sysdate,sysdate,0,'N',0,4,'java',1);
INSERT INTO board values(2,1,1,1,1,'두번째입니다.','심시해요 ㅋㅋㅋㅋㅋ',sysdate,sysdate,0,'N',0,4,'java',1);



/***********************************************************************************/
public void insertAttachment(AttachmentVO attachmentVO);
/***********************************************************************************/
파일경로 어디로??




/***********************************************************************************/
public void insertComments(CommentsVO commentsVO);
/***********************************************************************************/
create table comments (
	commentsId number primary key,
	comments varchar2(1000) not null,
	contentId number not null,
	memberId varchar2(30) not null,
	regDate date not null,
	modDate date not null,
	foreign key(contentId) REFERENCES board(contentId) on delete cascade,
	foreign key(memberId) REFERENCES member(memberId)
);

INSERT INTO comments VALUES(1,'저도 반갑습니다. ㅋㅋㅋㅋㅋ',1,'java',sysdate,sysdate);
INSERT INTO comments VALUES(2,'ㅋㅋㅋ',1,'java',sysdate,sysdate);
INSERT INTO comments VALUES(3,'ㅎㅎㅎ',1,'java',sysdate,sysdate);



/***********************************************************************************/
public String getDate(int seq);
/***********************************************************************************/
SELECT to_char(regDate,'yyyy-mm-dd:hh24:mi:ss') FROM board WHERE contentId=seq; 




/***********************************************************************************/
public BoardVO getContent(int contentId);
/***********************************************************************************/
불필요 Column : modDate(?) 

SELECT contentId, groupId, groupPosition, groupDepth, category, title, content, to_char(regDate,'yyyy-mm-dd:hh24:mi:ss')
	,hits, commentHits, boardId, memberId, locationId from board where contentId=#contentId#;




/***********************************************************************************/
public AttachmentVO getAttachment(int contentId);
/***********************************************************************************/
파일관련 학습필요



/***********************************************************************************/
public ArrayList<BoardVO> getContentList(int locationId, int boardId, int page);
/***********************************************************************************/
	SELECT rownum,page,groupId,groupPosition,isNotice, groupDepth, category, title, to_char(regDate,'MM/DD') as regDate,contentId, hits, commentsHits, memberId 
		FROM (SELECT contentId, groupId, groupPosition, groupDepth
			, category, title, regDate, hits, commentsHits, isNotice, memberId
			, CEIL(rownum/15) AS page 
			FROM (SELECT * FROM board order by isNotice desc, max(groupId) OVER(partition by groupId) desc, groupPosition)) 
		
SELECT max(groupId) FROM board WHERE locationId=1 AND boardId=4		
CREATE INDEX groupId_idx ON board(groupId); 
CREATE INDEX isNotice_idx ON board(isNotice);


/***********************************************************************************/
public ArrayList<CommentsVO> getCommentsList(int contentId);
/***********************************************************************************/
create table comments (
	commentsId number primary key,
	comments varchar2(1000) not null,
	contentId number not null,
	memberId varchar2(30) not null,
	regDate date not null,
	modDate date not null,
	foreign key(contentId) REFERENCES board(contentId) on delete cascade,
	foreign key(memberId) REFERENCES member(memberId)
);
불필요 column : modDate

SELECT commentsId, comments, memberId, regDate FROM comments WHERE contentsId=#contentsId# 



/***********************************************************************************/
public ArrayList<BoardVO> findContentByName(String name, int boardId, int locationId);
/***********************************************************************************/





/***********************************************************************************/
public ArrayList<BoardVO> findContentByTitle(String title, int boardId, int locationId);
/***********************************************************************************/




/***********************************************************************************/
public void updateContent(BoardVO boardVO);
/***********************************************************************************/




/***********************************************************************************/
public void updateHits(int contentId);
/***********************************************************************************/




/***********************************************************************************/
public void deleteContent(int contentId);
/***********************************************************************************/



/***********************************************************************************/
public void deleteAttachment(int contentId);
/***********************************************************************************/




/***********************************************************************************/
public void deleteComments(int commentsId);
/***********************************************************************************/



/***********************************************************************************/
public void deleteAllCommentsByContentId(int contentId);
/***********************************************************************************/




/***********************************************************************************/

SELECT count(*) FROM board
alter table board rename column commenthits to commentshits

